package database

import (
	"fmt"
	"reflect"
	"strings"

	"gorm.io/gorm"
	"gorm.io/gorm/schema"
)

// GenerateCreateTableSQL 根据 GORM 模型生成 CREATE TABLE SQL
// 这是对外提供的简化版本，接受 *gorm.DB 和模型
func GenerateCreateTableSQL(db *gorm.DB, model interface{}) (string, error) {
	stmt := &gorm.Statement{DB: db}
	if err := stmt.Parse(model); err != nil {
		return "", fmt.Errorf("解析模型失败: %w", err)
	}

	return generateCreateTableSQLFromStatement(stmt, model), nil
}

// generateCreateTableSQLFromStatement 从 GORM Statement 生成 SQL
func generateCreateTableSQLFromStatement(stmt *gorm.Statement, model interface{}) string {

	// 获取表名
	tableName := stmt.Schema.Table
	if tableName == "" {
		// 尝试从 TableName() 方法获取
		if tn, ok := model.(interface{ TableName() string }); ok {
			tableName = tn.TableName()
		} else {
			// 使用模型类型名的小写复数形式
			modelType := reflect.TypeOf(model)
			if modelType.Kind() == reflect.Ptr {
				modelType = modelType.Elem()
			}
			tableName = strings.ToLower(modelType.Name()) + "s"
		}
	}

	var sqlBuilder strings.Builder
	sqlBuilder.WriteString(fmt.Sprintf("CREATE TABLE IF NOT EXISTS `%s` (\n", tableName))

	// 处理字段
	fields := make([]string, 0)
	indexes := make([]string, 0)
	uniqueIndexes := make(map[string][]string)

	if stmt.Schema != nil {
		for _, field := range stmt.Schema.Fields {
			fieldSQL := generateFieldSQL(field)
			if fieldSQL != "" {
				fields = append(fields, "    "+fieldSQL)
			}

			// 处理索引
			if field.TagSettings["INDEX"] != "" {
				indexName := field.TagSettings["INDEX"]
				if indexName == "" {
					indexName = fmt.Sprintf("idx_%s", field.DBName)
				}
				indexes = append(indexes, fmt.Sprintf("    INDEX `%s` (`%s`)", indexName, field.DBName))
			}

			// 处理唯一索引
			if field.TagSettings["UNIQUEINDEX"] != "" {
				idxName := field.TagSettings["UNIQUEINDEX"]
				if idxName == "" {
					idxName = fmt.Sprintf("idx_%s", field.DBName)
				}
				uniqueIndexes[idxName] = append(uniqueIndexes[idxName], field.DBName)
			}
		}

		// 处理主键
		if len(stmt.Schema.PrimaryFields) > 0 {
			pkFields := make([]string, len(stmt.Schema.PrimaryFields))
			for i, field := range stmt.Schema.PrimaryFields {
				pkFields[i] = fmt.Sprintf("`%s`", field.DBName)
			}
			// 主键已经在字段定义中，只需要确保 AUTO_INCREMENT
		}
	} else {
		// 如果 Schema 为空，使用反射解析
		modelType := reflect.TypeOf(model)
		if modelType.Kind() == reflect.Ptr {
			modelType = modelType.Elem()
		}

		for i := 0; i < modelType.NumField(); i++ {
			field := modelType.Field(i)
			fieldSQL := generateFieldSQLFromReflect(field)
			if fieldSQL != "" {
				fields = append(fields, "    "+fieldSQL)
			}
		}
	}

	// 添加字段
	sqlBuilder.WriteString(strings.Join(fields, ",\n"))

	// 添加唯一索引
	for idxName, cols := range uniqueIndexes {
		if len(cols) == 1 {
			sqlBuilder.WriteString(fmt.Sprintf(",\n    UNIQUE KEY `%s` (`%s`)", idxName, cols[0]))
		} else {
			sqlBuilder.WriteString(fmt.Sprintf(",\n    UNIQUE KEY `%s` (`%s`)", idxName, strings.Join(cols, "`, `")))
		}
	}

	// 添加普通索引
	for _, idx := range indexes {
		sqlBuilder.WriteString(",\n" + idx)
	}

	sqlBuilder.WriteString("\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci")

	// 添加表注释
	modelType := reflect.TypeOf(model)
	if modelType.Kind() == reflect.Ptr {
		modelType = modelType.Elem()
	}
	tableComment := getTableComment(modelType.Name())
	if tableComment != "" {
		sqlBuilder.WriteString(fmt.Sprintf(" COMMENT='%s'", tableComment))
	}

	sqlBuilder.WriteString(";")

	return sqlBuilder.String()
}

// generateFieldSQL 生成字段 SQL
func generateFieldSQL(field *schema.Field) string {
	fieldName := field.DBName
	if fieldName == "" {
		return ""
	}

	var parts []string
	parts = append(parts, fmt.Sprintf("`%s`", fieldName))

	// 类型
	var dataType string
	if field.TagSettings["TYPE"] != "" {
		dataType = field.TagSettings["TYPE"]
	} else if field.DataType != "" {
		dataType = string(field.DataType)
	} else {
		dataType = getMySQLType(field.FieldType)
	}

	parts = append(parts, dataType)

	// 主键
	if field.PrimaryKey {
		if strings.Contains(strings.ToLower(dataType), "int") {
			parts = append(parts, "AUTO_INCREMENT")
		}
		parts = append(parts, "PRIMARY KEY")
	}

	// NOT NULL
	hasNotNull := field.TagSettings["NOT NULL"] != "" || strings.Contains(field.Tag.Get("gorm"), "not null")
	if hasNotNull && !field.PrimaryKey {
		parts = append(parts, "NOT NULL")
	} else if !field.PrimaryKey {
		// 检查字段类型是否为指针（指针类型通常可为 NULL）
		isPointer := field.FieldType.Kind() == reflect.Ptr
		if !isPointer && !strings.Contains(dataType, "NULL") && !strings.Contains(strings.ToLower(dataType), "timestamp") {
			parts = append(parts, "NOT NULL")
		}
	}

	// DEFAULT
	if field.TagSettings["DEFAULT"] != "" {
		defaultValue := field.TagSettings["DEFAULT"]
		parts = append(parts, fmt.Sprintf("DEFAULT %s", defaultValue))
	} else if field.HasDefaultValue {
		if field.DefaultValue != "" {
			parts = append(parts, fmt.Sprintf("DEFAULT '%s'", field.DefaultValue))
		}
	}

	// COMMENT
	if field.Comment != "" {
		parts = append(parts, fmt.Sprintf("COMMENT '%s'", field.Comment))
	}

	return strings.Join(parts, " ")
}

// generateFieldSQLFromReflect 从反射生成字段 SQL
func generateFieldSQLFromReflect(field reflect.StructField) string {
	// 跳过非导出字段
	if !field.IsExported() {
		return ""
	}

	// 解析 gorm tag
	gormTag := field.Tag.Get("gorm")
	if gormTag == "" {
		return ""
	}

	fieldName := getFieldName(field)
	if fieldName == "" {
		return ""
	}

	var parts []string
	parts = append(parts, fmt.Sprintf("`%s`", fieldName))

	// 类型
	dataType := getMySQLType(field.Type)
	if strings.Contains(gormTag, "type:") {
		// 从 tag 提取类型
		tagParts := strings.Split(gormTag, ";")
		for _, part := range tagParts {
			if strings.HasPrefix(part, "type:") {
				dataType = strings.TrimPrefix(part, "type:")
				break
			}
		}
	}

	parts = append(parts, dataType)

	// 主键
	if strings.Contains(gormTag, "primarykey") {
		if strings.Contains(strings.ToLower(dataType), "int") {
			parts = append(parts, "AUTO_INCREMENT")
		}
		parts = append(parts, "PRIMARY KEY")
	}

	// NOT NULL
	if strings.Contains(gormTag, "not null") {
		parts = append(parts, "NOT NULL")
	}

	return strings.Join(parts, " ")
}

// getFieldName 获取字段数据库名称
func getFieldName(field reflect.StructField) string {
	gormTag := field.Tag.Get("gorm")
	if gormTag != "" {
		// 解析 column:xxx
		tagParts := strings.Split(gormTag, ";")
		for _, part := range tagParts {
			if strings.HasPrefix(part, "column:") {
				return strings.TrimPrefix(part, "column:")
			}
		}
	}

	// 使用字段名的 snake_case
	return toSnakeCase(field.Name)
}

// getMySQLType 根据 Go 类型获取 MySQL 类型
func getMySQLType(typ reflect.Type) string {
	switch typ.Kind() {
	case reflect.Bool:
		return "TINYINT(1)"
	case reflect.Int, reflect.Int8:
		return "TINYINT"
	case reflect.Int16:
		return "SMALLINT"
	case reflect.Int32:
		return "INT"
	case reflect.Int64, reflect.Uint64:
		return "BIGINT"
	case reflect.Uint, reflect.Uint8:
		return "TINYINT UNSIGNED"
	case reflect.Uint16:
		return "SMALLINT UNSIGNED"
	case reflect.Uint32:
		return "INT UNSIGNED"
	case reflect.Float32:
		return "FLOAT"
	case reflect.Float64:
		return "DOUBLE"
	case reflect.String:
		return "VARCHAR(255)"
	case reflect.Struct:
		if typ.Name() == "Time" {
			return "TIMESTAMP"
		}
		return "TEXT"
	case reflect.Slice, reflect.Array:
		return "BLOB"
	default:
		return "VARCHAR(255)"
	}
}

// toSnakeCase 转换为 snake_case
func toSnakeCase(s string) string {
	var result strings.Builder
	for i, r := range s {
		if i > 0 && r >= 'A' && r <= 'Z' {
			result.WriteByte('_')
		}
		result.WriteRune(r)
	}
	return strings.ToLower(result.String())
}

// getTableComment 获取表注释（简化版）
func getTableComment(tableName string) string {
	// 移除 Model 后缀
	name := strings.TrimSuffix(tableName, "Model")
	// 转换为中文（这里简化处理）
	return name + "表"
}

// GenerateDropTableSQL 生成 DROP TABLE SQL
func GenerateDropTableSQL(db *gorm.DB, model interface{}) (string, error) {
	stmt := &gorm.Statement{DB: db}
	if err := stmt.Parse(model); err != nil {
		return "", fmt.Errorf("解析模型失败: %w", err)
	}

	tableName := stmt.Schema.Table
	if tableName == "" {
		if tn, ok := model.(interface{ TableName() string }); ok {
			tableName = tn.TableName()
		} else {
			modelType := reflect.TypeOf(model)
			if modelType.Kind() == reflect.Ptr {
				modelType = modelType.Elem()
			}
			tableName = strings.ToLower(modelType.Name()) + "s"
		}
	}

	return fmt.Sprintf("DROP TABLE IF EXISTS `%s`;", tableName), nil
}
